package com.uziot.bucket.poi.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 功能描述: <br>
 * 通过excel上传导入表格，从表格中获取参数信息
 * 工具类
 *
 * @author shidt
 * @date 2019/11/11 17:52
 */
@Slf4j
public class ImportExcel {
    private ImportExcel() {
        throw new IllegalStateException("Utility class");
    }

    /**
     * 通过列和字段读取信息,此方法是入口方法
     * 列字段例如String[] columns = {"name", "age", "score"}用于接收第一列、第二列。。。的参数;
     * 文件路径例如
     * String filePath="D:\test.xlsx";
     * 返回的list每一个值都是一行读取的数据，里面的map代表本行的表格输入框的参数名和参数值
     *
     * @param startLine 开始读取的行数
     * @param columns   columns
     */
    public static List<Map<String, String>> readExcelAsMap(File file, String[] columns, int startLine)
            throws IOException {
        InputStream inputStream = readExcelStream(file);
        return readExcelAsMap(inputStream, columns, startLine);
    }

    public static List<Map<String, String>> readExcelAsMap(String filePath, String[] columns, int startLine)
            throws IOException {
        File file = new File(filePath);
        return readExcelAsMap(file, columns, startLine);
    }

    public static List<Map<String, String>> readExcelAsMap(InputStream inputStream, String[] columns, int startLine)
            throws IOException {
        String cellData;
        Workbook wb = readExcel(inputStream);
        //用来存放表中数据
        List<Map<String, String>> list = new ArrayList<>();
        //获取第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        //获取最大行数
        int rownum = sheet.getPhysicalNumberOfRows();
        //获取第一行
        Row row = sheet.getRow(0);
        //获取最大列数
        int colnum = row.getPhysicalNumberOfCells();
        log.info("获取最大列数：" + colnum + "定义最大列数：" + columns.length);
        if (colnum > columns.length) {
            throw new RuntimeException("模板定义宽度上传宽度不相符，解析发生异常，请检查模板是否上传正确！");
        }
        for (int i = startLine; i < rownum; i++) {
            Map<String, String> map = new LinkedHashMap<>();
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < colnum; j++) {
                    cellData = (String) getCellFormatValue(row.getCell(j));
                    map.put(columns[j], cellData);
                }
            } else {
                break;
            }
            String tableTitle = (String) getCellFormatValue(sheet.getRow(0).getCell(0));
            map.put("tableTitle", tableTitle);
            list.add(map);
        }
        // 关闭流
        if (inputStream != null) {
            inputStream.close();
        }
        return list;
    }

    /**
     * 读取excel工具类
     *
     * @param filePath filePath
     * @return Workbook
     */
    public static Workbook readExcel(String filePath) {
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        try (InputStream is = new FileInputStream(filePath)) {

            if (".xls".equals(extString)) {
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return new XSSFWorkbook(is);
            } else {
                return null;
            }

        } catch (IOException e) {
            log.error("读取Excel失败！", e);
        }
        return null;
    }

    /**
     * 将输入流转换成工作簿对象
     *
     * @param is is
     * @return Workbook
     * @throws IOException IOException
     */
    private static Workbook readExcel(InputStream is) throws IOException {
        return new XSSFWorkbook(is);
    }

    private static InputStream readExcelStream(File file) throws IOException {
        return new FileInputStream(file);
    }

    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }


    public static List<Map<String, String>> checkExcelRow(InputStream inputStream, String[] columns, int startLine)
            throws IOException {
        //用来存放表中数据
        List<Map<String, String>> list = new ArrayList<>();
        String cellData;
        Workbook wb = readExcel(inputStream);
        //获取第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        //获取最大行数
        int rownum = sheet.getPhysicalNumberOfRows();
        //获取第一行
        Row row = sheet.getRow(0);
        //获取最大列数
        int colnum = row.getPhysicalNumberOfCells();
        log.info("获取最大列数：" + colnum + "定义最大列数：" + columns.length);
        if (colnum > columns.length) {
            throw new RuntimeException("您定义的读取列数过小，上传文件的列数过大！");
        }
        for (int i = startLine; i < rownum; i++) {
            Map<String, String> map = new LinkedHashMap<>();
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < colnum; j++) {
                    Cell cell = row.getCell(j);
                    cellData = (String) getCellFormatValue(cell);
                    map.put(columns[j], cellData);
                }
            } else {
                break;
            }
            String tableTitle = (String) getCellFormatValue(sheet.getRow(0).getCell(0));
            map.put("tableTitle", tableTitle);
            list.add(map);
        }
        // 关闭流
        if (inputStream != null) {
            inputStream.close();
        }
        return list;
    }

}
